package com.mmy.dao.plane;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mmy.domain.plane.VPlaneBase;

public interface VPlaneBaseRepository extends JpaRepository<VPlaneBase, Integer> {

	/*
	 * 根据name查找
	 */
	@Query(value = "select *  from vPlaneBase where cPlaneName like %:strPlaneName%", nativeQuery = true)
	List<VPlaneBase> findByName(@Param("strPlaneName") String strPlaneName);

	/*
	 * 根据id查找
	 */
	@Query(value = "select *  from vPlaneBase where iPlaneID = :iPlaneID", nativeQuery = true)
	VPlaneBase findByID(@Param("iPlaneID") int iPlaneID);

	/**
	 * 后台航班信息管理中显示自己公司的航班信息
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.03.17
	 */
	@Query(value = "select * from vPlaneBase where cSpare1=:iOrgID ", nativeQuery = true)
	List<VPlaneBase> findByOrgID(int iOrgID);

	/**
	 * 后台航班信息管理中超级管理员登录显示所有航班信息
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.03.17
	 */
	@Query(value = "select * from vPlaneBase where (:strPlaneName = \"\" or cPlaneName like %:strPlaneName%) "
			+ "and (:strPlaneOrgName = \"\" or cOrgName like %:strPlaneOrgName%) "
			+ "and (:strTravelOrgName = \"\" or cTravelOrgName like %:strTravelOrgName%) limit :startIndex,:count", nativeQuery = true)
	List<VPlaneBase> findSearchALL(@Param("strPlaneName") String strPlaneName,
			@Param("strPlaneOrgName") String strPlaneOrgName, @Param("strTravelOrgName") String strTravelOrgName,
			@Param("startIndex") int startIndex, @Param("count") int count);

	/**
	 * 后台航班信息管理中超级管理员登录显示所有公司的航班信息 获取分页
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.02.22
	 */
	@Query(value = "select count(*)  from vPlaneBase where (:strPlaneName = \"\" or cPlaneName like %:strPlaneName%) "
			+ "and (:strPlaneOrgName = \"\" or cOrgName like %:strPlaneOrgName%) "
			+ "and (:strTravelOrgName = \"\" or cTravelOrgName like %:strTravelOrgName%)", nativeQuery = true)
	int countALL(@Param("strPlaneName") String strPlaneName, @Param("strPlaneOrgName") String strPlaneOrgName,
			@Param("strTravelOrgName") String strTravelOrgName);

}
